package db.jdbc;

import db.ActivityApplicationRepository;
import db.ClubRepository;
import domain.Activity;
import domain.ActivityApplication;
import domain.ClubAdministrator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import web.PaginationSupport;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;

/**
 * 活动申请资源库接口的jdbc实现类
 *
 * @author liujiale
 */
@Repository
public class JdbcActivityApplicationRepository implements ActivityApplicationRepository {
    private static final String INSERT_ActivityApplication = "insert into activityApplication(applicationTime,status,description) values(?,?,?)";
    private static final String INSERT_activity_activityApplication = "insert into activity_activityApplication(activityID,activityApplicationID) values(?,?)";
    private static final String INSERT_clubAdministrators_activityApplication = "insert into clubAdministrators_activityApplication(userID,activityApplicationID) values(?,?)";

    private static final String DELETE_ActivityApplication = "delete from activityApplication where activityApplicationID=?";
    private static final String DELETE_activity_activityApplication = "delete from activity_activityApplication where activityApplicationID=?";
    private static final String DELETE_clubAdministrators_activityApplication = "delete from clubAdministrators_activityApplication where activityApplicationID=?";

    private static final String UPDATE_ActivityApplication = "update activityApplication set status=?,description=? where activityApplicationID=?";
    private static final String SELECT_ActivityApplication = "select * from activityApplication";
    private static final String SELECT_ActivityApplication_Pagination = SELECT_ActivityApplication + " order by activityApplicationID  limit ? offset  ?";

    private static JdbcTemplate jdbc;

    @Autowired
    public JdbcActivityApplicationRepository(JdbcTemplate jdbc) {this.jdbc = jdbc;}

    /**
     * 获取申请的总数
     *
     * @return 申请的数量
     */
    @Override
    public long count() {
        return jdbc.queryForInt("select count(activityApplicationID) from activityApplication");
    }

    /**
     * 创建一个新的申请
     *
     * @param activityApplication 新申请的信息
     * @return 新创建的申请
     */
    @Override
    public ActivityApplication save(ActivityApplication activityApplication) {
        Date tmpTime = new Date();
        Activity tmp = activityApplication.getPendingActivity();
        //申请者ID
        int applicantID = ((ClubAdministrator) activityApplication.getApplicant()).getClubAdminID();
        //新建立的活动申请ID
        int actApplicationID = jdbc.queryForInt(INSERT_ActivityApplication+" returning activityapplicationID", tmpTime, 0, activityApplication.getDescription());
        //新建立的活动的ID
        int actID = jdbc.queryForInt("insert into activity(activityName, description, starttime, endtime, place) values(?,?,?,?,?) returning activityid",
                tmp.getActivityName(), tmp.getDescription(), tmp.getStartTime(), tmp.getEndTime(), tmp.getPlace());
        jdbc.update(INSERT_activity_activityApplication, actID, actApplicationID);
        jdbc.update(INSERT_clubAdministrators_activityApplication, applicantID, actApplicationID);
        return activityApplication;
    }

    /**
     * 根据ID查找申请
     *
     * @param activityApplicationID 申请的ID
     * @return 对应ID的申请
     */
    @Override
    public ActivityApplication findOne(int activityApplicationID) {
        return jdbc.queryForObject(SELECT_ActivityApplication + " where activityApplicationID=?", new ActivityApplicationRowMapper(), activityApplicationID);
    }

    /**
     * 更新申请
     *
     * @param activityApplication 更新后的申请信息
     * @return 是否更新成功
     */
    @Override
    public boolean updateActivityApplication(ActivityApplication activityApplication) {
        return jdbc.update(UPDATE_ActivityApplication, activityApplication.getStatus(), activityApplication.getDescription(), activityApplication.getActivityApplicationID()) != 0;
    }

    /**
     * 分页查找申请
     *
     * @param pageNo   页码
     * @param pageSize 每页的数量
     * @return 分页的申请列表
     */
    @Override
    public PaginationSupport<ActivityApplication> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<ActivityApplication>(new ArrayList<ActivityApplication>(0), 0);
        }

        List<ActivityApplication> items = jdbc.query(SELECT_ActivityApplication_Pagination, new ActivityApplicationRowMapper(), pageSize, startIndex);
        return new PaginationSupport<ActivityApplication>(items, totalCount, pageSize, startIndex);
    }

    /**
     * 删除申请
     *
     * @param activityApplicationID 申请的ID
     * @return 是否删除成功
     */
    @Override
    public boolean deleteActivityApplication(int activityApplicationID) {
        ActivityApplication activityApplication=findOne(activityApplicationID);
        if(activityApplication==null){
            return false;
        }
        jdbc.update(DELETE_activity_activityApplication,activityApplicationID);
        jdbc.update(DELETE_clubAdministrators_activityApplication,activityApplicationID);
        return jdbc.update(DELETE_ActivityApplication,activityApplicationID)!=0;
    }

    /**
     * 获取所有申请
     *
     * @return 所有申请的列表
     */
    @Override
    public List<ActivityApplication> findAll() {
        return jdbc.query(SELECT_ActivityApplication+" order by activityApplicationID",new ActivityApplicationRowMapper());
    }

    static class ActivityApplicationRowMapper implements RowMapper<ActivityApplication> {
        /**
         * 将数据库查询结果映射到ActivityApplication对象
         *
         * @param rs 查询结果集
         * @param i 行号
         * @return ActivityApplication对象
         * @throws SQLException SQL异常
         */
        public ActivityApplication mapRow(ResultSet rs, int i) throws SQLException {

            // 获取activityApplication字段，并构造ActivityApplication对象
            int activityApplicationID =  rs.getInt("activityApplicationID");
            ActivityApplication activityApplication = new ActivityApplication(
                    rs.getDate("applicationTime"),
                    rs.getInt("status"),
                    rs.getString("description"),
                    activityApplicationID);

            // 查询申请人信息并设置到ActivityApplication
            ClubAdministrator caAdministrator = jdbc.queryForObject("select ca.* from clubAdministrator as ca, clubadministrators_activityapplication as cc where ca.clubAdminID = cc.userID and cc.activityApplicationID=?",
                    new JdbcClubAdministratorRepository.ClubAdministratorRowMapper(),activityApplicationID);
            activityApplication.setApplicant(caAdministrator);

            // 查询申请的活动信息并设置到ActivityApplication
            Activity activity = jdbc.queryForObject("select a.* from activity as a,activity_activityapplication as aa where a.activityID=aa.activityID and aa.activityApplicationID=?",
                    new JdbcActivityRepository.ActivityRowMapper(),activityApplicationID);
            Vector<ClubAdministrator> orgList = new Vector<>();
            orgList.add(caAdministrator);
            activity.setOrganizerList(orgList);
            activityApplication.setPendingActivity(activity);

            // 返回构造的ActivityApplication对象
            return activityApplication;
        }
    }

}
